﻿ALTER PROCEDURE [dbo].[PromoteMancoDocs]
	@ManCoDocIDs VARCHAR(MAX),
	@SourceAppEnvironment VARCHAR(50),  
	@TargetAppEnvironment VARCHAR(50),    
	@UserID NVARCHAR(128),	
	@Comment NVARCHAR(500)=NULL    
AS
BEGIN
	SET NOCOUNT ON;
	BEGIN TRANSACTION [PromoteMancoDoc]		
	
	DECLARE @MancoDocIdsTable table(sourceMancoDocId INT)	
	
	DECLARE @SourceAppEnvironmentID INT;
	DECLARE @TargetAppEnvironmentID INT;
	
	DECLARE @sourceEnvironmentTypeId INT;
	DECLARE @targetEnvironmentTypeId INT;
	
	SELECT TOP 1 @sourceEnvironmentTypeId=EnvironmentID,@SourceAppEnvironmentID=ae.AppEnvironmentID
	FROM EnvironmentType et
	INNER JOIN AppEnvironment ae ON ae.AppEnvironmentID=et.AppEnvironmentID
	WHERE ae.Name=@SourceAppEnvironment
	
	SELECT TOP 1 @targetEnvironmentTypeId=EnvironmentID,@TargetAppEnvironmentID=ae.AppEnvironmentID FROM EnvironmentType et
	INNER JOIN AppEnvironment ae ON ae.AppEnvironmentID=et.AppEnvironmentID
	WHERE ae.Name=@TargetAppEnvironment
	
	INSERT INTO @MancoDocIdsTable
	SELECT SplitValue FROM [dbo].[Split](@ManCoDocIDs, ',')
    
	WHILE((SELECT COUNT(*) FROM @MancoDocIdsTable )>0)
	Begin
	DECLARE @sourceMancoDocId INT;
	DECLARE @existingMancoDocId INT;
	DECLARE @sourceMancoDocURI UNIQUEIDENTIFIER;
	
	SET @sourceMancoDocId=(SELECT TOP 1 sourceMancoDocId FROM @MancoDocIdsTable)
	SET @sourceMancoDocURI=(SELECT ManCoDocURI FROM ManCoDocs WHERE ManCoDocID=@sourceMancoDocId)

	SET @existingMancoDocId=
	(
		SELECT ManCoDocID FROM ManCoDocs 
		WHERE ManCoDocURI=@sourceMancoDocURI 
		AND EnvironmentID=@targetEnvironmentTypeId AND Active=1
	)

	-- Deactivate a previously published ManCoDoc
	IF(@existingMancoDocId>0)
	BEGIN
		update dbo.ManCoDocs set Active=0 where ManCoDocID=@existingMancoDocId
	END		
	
	DECLARE @newMancoDocId INT
	DECLARE @tempMancoDocTable TABLE (Id INT)
	
	-- Copy the source ManCoDoc with the new EnvironmentId
	INSERT INTO ManCoDocs ([BravuraDocID],[ManCoID],[DocumentTypeID],[PubFileName],[EnvironmentID]
	,[Version],[VersionComments],[Active],[Authorise],[ManualSubmissionOnly],[DateFolderCreation],[ManCoDocURI])
	OUTPUT inserted.ManCoDocID INTO @tempMancoDocTable
	SELECT [BravuraDocID],[ManCoID],[DocumentTypeID],[PubFileName],@targetEnvironmentTypeId
	,[Version],[VersionComments],[Active],[Authorise],[ManualSubmissionOnly],[DateFolderCreation],[ManCoDocURI]
	FROM ManCoDocs WHERE ManCoDocID=@sourceMancoDocId		
		
	SELECT @newMancoDocId=Id FROM @tempMancoDocTable

	IF(@existingMancoDocId>0) -- If ManCoDoc was previously published	
	BEGIN
		-- Copy GroupDataRights from previously published ManCoDoc
		INSERT INTO dbo.GroupDataRights ([GroupID],[ManCoDocID],[EnvironmentID])
		SELECT [GroupID],@newMancoDocId,@TargetAppEnvironmentID
		FROM dbo.GroupDataRights WHERE ManCoDocID=@existingMancoDocId
	END
	ELSE
	BEGIN
		-- Copy GroupDataRights from source ManCoDoc
		INSERT INTO dbo.GroupDataRights ([GroupID],[ManCoDocID],[EnvironmentID])
		SELECT [GroupID],@newMancoDocId,@TargetAppEnvironmentID 
		FROM dbo.GroupDataRights WHERE ManCoDocID=@sourceMancoDocId
	END
	
	--Insert LayoutArchiveFields
	Insert into dbo.LayoutArchiveFields ([LayoutFileID],[LayoutFileType],[ManCoDocID],[FieldName],[ArchiveIndexFieldName]
	,[FieldType],[Offset],[Length],[FileNameOrder],[TrimFieldSeparator])
	select [LayoutFileID],[LayoutFileType],@newMancoDocId,[FieldName],[ArchiveIndexFieldName]
	,[FieldType],[Offset],[Length],[FileNameOrder],[TrimFieldSeparator]
	from  dbo.LayoutArchiveFields where ManCoDocID=@sourceMancoDocId
		
	--Insert HPExstreamReferenceFiles
	Insert into dbo.HPExstreamReferenceFiles ([ManCoDocID],[HPExstreamSymbolicName],[ReferenceFilePath],[MediaReferenceFile])
	select @newMancoDocId,[HPExstreamSymbolicName],[ReferenceFilePath],[MediaReferenceFile]
	from dbo.HPExstreamReferenceFiles where ManCoDocID=@sourceMancoDocId
		
	--Insert HPExstreamControlFileSwitch
	Insert into dbo.HPExstreamControlFileSwitch ([ManCoDocID],[ControlFileSwitch])
	select @newMancoDocId,[ControlFileSwitch] 
	from dbo.HPExstreamControlFileSwitch where ManCoDocID=@sourceMancoDocId
		
	--Insert IndexDefinitions
	DECLARE @newIndexDefId int
	DECLARE @newIndexDefinitionTable table (Id int)
		
	DECLARE @indexDefId int
	DECLARE @tempIndexDefinitionTable table (Id int)
		
	insert into @tempIndexDefinitionTable
	Select IndexDefinitionID from dbo.IndexDefinition where ManCoDocID=@sourceMancoDocId
		
	While((select COUNT(*) from @tempIndexDefinitionTable )>0)
	Begin			
		select top 1 @indexDefId=Id from @tempIndexDefinitionTable
		--Insert IndexDefinition
		insert into dbo.IndexDefinition ([ManCoDocID],[IndexDefinitionName],[ExstreamSymbolicIndexName],[ExstreamSymbolicDataName],[ExstreamSymbolicReportName]
		,[IndexDescription],[ReportIsArchive],[LayoutFileID] ,[ReportLayoutFileID],[MailSortSettingID])
		output inserted.IndexDefinitionID into @newIndexDefinitionTable
		select @newMancoDocId,[IndexDefinitionName],[ExstreamSymbolicIndexName],[ExstreamSymbolicDataName],[ExstreamSymbolicReportName]
		,[IndexDescription],[ReportIsArchive],[LayoutFileID] ,[ReportLayoutFileID],[MailSortSettingID]
			from dbo.IndexDefinition where IndexDefinitionID=@indexDefId
			
		select @newIndexDefId=Id from @newIndexDefinitionTable
			
		--Insert Sort rows
		Insert into dbo.Sort ([SortField],[DirectionTypeID],[SortOrder],[IndexDefinitionID])
		select [SortField],[DirectionTypeID],[SortOrder],@newIndexDefId 
		from dbo.Sort where IndexDefinitionID=@indexDefId
			
		--Get MediaDefinitions and Insert new MediaDefinitions for each IndexDefinition
		DECLARE @newMediaDefId int
		DECLARE @newMediaDefinitionTable table (Id int)
			
		DECLARE @mediaDefId int
		DECLARE @tempMediaDefinitionTable table (Id int)
			
		insert into @tempMediaDefinitionTable
		Select MediaDefID from dbo.MediaDefinition where IndexDefinitionID=@indexDefId
			
		While((select COUNT(*) from @tempMediaDefinitionTable )>0)
		Begin
			select Top 1 @mediaDefId=Id from @tempMediaDefinitionTable
			--Insert MediaDefinition
			Insert Into dbo.MediaDefinition ([QueueName],[QueueNameSymbollic],[ManCoDocID],[IndexDefinitionID],[QueueDescription],[Hold]
			,[BatchSizeSheets],[MailsortSettingID],[FinishingDefID],[SortID],[Print],[PDF_Only],[createDate],[createUserID],[OutputFilename]
			,[OldQueueName],[ReportHPExstreamSymbolicName],[ReportLayoutFileID],[ReportIsArchive],[ReturnReportInDJA],[ReturnOutput]
			,[ShowOnShortReport],[ShowPDFOnWebPortal],[ShowReportOnWebPortal],[IsCD],[Colour],[EncloseEnvelope],[Action])
			output inserted.MediaDefID into @newMediaDefinitionTable
			select [QueueName],[QueueNameSymbollic],@newMancoDocId,@newIndexDefId,[QueueDescription],[Hold]
			,[BatchSizeSheets],[MailsortSettingID],[FinishingDefID],[SortID],[Print],[PDF_Only],GETDATE(),@UserID,[OutputFilename]
			,[OldQueueName],[ReportHPExstreamSymbolicName],[ReportLayoutFileID],[ReportIsArchive],[ReturnReportInDJA],[ReturnOutput]
			,[ShowOnShortReport],[ShowPDFOnWebPortal],[ShowReportOnWebPortal],[IsCD],[Colour],[EncloseEnvelope],[Action]
			from dbo.MediaDefinition where MediaDefID=@mediaDefId
				
			select @newMediaDefId=Id from @newMediaDefinitionTable
				
			--Insert MediaStock
			Insert into dbo.MediaStock ([StockID],[MediaDefID],[PublishDate],[StationNumber])
			select [StockID],@newMediaDefId,[PublishDate],[StationNumber] 
			from dbo.MediaStock where MediaDefID=@mediaDefId
				
			--Insert AdditionalReporting
			Insert into dbo.AdditionalReporting ([HPExstreamSymbolicName],[ReportLayoutFileID],[ReportDescription]
			,[MediaDefinitionID],[OutputFileName],[ReturnReportInDJA],[ShowReportOnWebPortal],[ManCoDocID])
			select [HPExstreamSymbolicName],[ReportLayoutFileID],[ReportDescription]
			,@newMediaDefId,[OutputFileName],[ReturnReportInDJA],[ShowReportOnWebPortal],@newMancoDocId
			from dbo.AdditionalReporting where MediaDefinitionID=@mediaDefId and ManCoDocID=@sourceMancoDocId
				
			delete from @tempMediaDefinitionTable where Id=@mediaDefId
				
		End				
		delete from @tempIndexDefinitionTable where Id=@indexDefId
	End	
		
	Insert into DeploymentHistory ([ManCoDocID],[PromoteDate],[Label],[UserID],[Comment])
	values(@sourceMancoDocId,GETDATE(),'Pre-BBOS',@UserID,@Comment)
		
	delete from @MancoDocIdsTable where sourceMancoDocId=@sourceMancoDocId
	End
	COMMIT TRANSACTION [PromoteMancoDoc]
END